package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;

import org.json.JSONObject;

public class DatabaseUtility 
{
	PreparedStatement ps;
	ResultSet rs;
	static Connection conn = null;
	
	private Connection connect() throws Exception 
	{
		try 
		{
			String connectionURL = "jdbc:mysql://localhost:3306/db_mylooks";
			Connection connection = null;
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(connectionURL, "root", "greenteam");
			return connection;
		} 
		catch (SQLException e) 
		{ 
			System.out.println("SQL exc" +e.getMessage());
			throw e; 
		} 
		catch (Exception e) 
		{ 
			System.out.println("Exc" +e.getMessage());
			throw e; 
		}
	}
	
	public ResultSet GetResultSetLogin() throws Exception
	{
		if(conn == null || conn.isClosed()) conn = connect();
		
		if(conn!= null)
		{
			String STATEMENT = "SELECT * FROM login";						
			ps = conn.prepareStatement(STATEMENT);
			rs = ps.executeQuery();
		}
		
		return rs;
	}
	
	public boolean CheckLogin(String email, String password) throws Exception
	{
		if(conn == null || conn.isClosed()) conn = connect();
		
		if(conn!= null)
		{
			String STATEMENT = "SELECT pwd FROM login WHERE email = '"+email+"'";						
			ps = conn.prepareStatement(STATEMENT);
			rs = ps.executeQuery();
			
			if(rs.last())
			{
				rs.first();
				String db_password = rs.getString("pwd");
				if(password.equals(db_password))
				{
					conn.close();
					return true;
				}
			}
		}
		
		return false;
	}
	
	public JSONObject getUserData(String email) throws Exception
	{
		JSONObject user = new JSONObject();
		if(conn == null || conn.isClosed()) conn = connect();
		
		if(conn!= null)
		{
			String STATEMENT = "SELECT * FROM user WHERE email = '"+email+"'";						
			ps = conn.prepareStatement(STATEMENT);
			rs = ps.executeQuery();
			
			if(rs.last())
			{
				rs.first();
				user.put("email", rs.getString("email"));
				user.put("name", rs.getString("name"));
				user.put("surname", rs.getString("surname"));
				user.put("date_birth", rs.getString("date_birth"));
				user.put("gender", rs.getString("gender"));
				user.put("city", rs.getString("city"));
				/*ArrayList<String> fields_array = (ArrayList<String>) Arrays.asList(new String[]{"email","name","surname","date_birth","gender","city"});
				rs.first();
				for(int i=0;i<fields_array.size();i++)
					user.put(fields_array.get(i).toString(),rs.getString(fields_array.get(i).toString()));*/
			}
		}
		conn.close();
		return user;
	}
	
	public boolean Registration (String email, String name, String surname, String date_birth, String gender, String city, String photo ) throws Exception
	{
		if(conn == null || conn.isClosed()) conn = connect();
		
		if(conn!= null)
		{
			String QUERY = "INSERT INTO user(email,name,surname,date_birth,gender,city,photo) VALUES ('"+email+"','"+name+"','"+surname+"','"+date_birth+"','"+gender+"','"+city+"','"+photo.toString()+"')";						
			ps = conn.prepareStatement(QUERY);
			if(ps.executeQuery(QUERY) != null)
				return true;
		}
		return false;
	}
	
	public void CloseConnection()
	{
		if(conn != null)
		{
			try
			{
				conn.close();
			} 
			catch (SQLException e) 
			{
				System.out.println("Errore : " + e.getMessage());
			}
		}
	}
}


